﻿using System;
using System.Data;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

namespace Ztest
{
    public class ExcelOperator
    {
        public DataTable ReadExcelFile(string filePath)
        {
            DataTable dataTable = new DataTable();

            // 加载 Excel 文件
            using (FileStream fileStream = new FileStream(filePath, FileMode.Open))
            {
                HSSFWorkbook workbook = new HSSFWorkbook(fileStream);
                ISheet sheet = workbook.GetSheetAt(0);

                // 读取表头行
                IRow headerRow = sheet.GetRow(0);
                for (int i = 0; i < headerRow.LastCellNum; i++)
                {
                    dataTable.Columns.Add(headerRow.GetCell(i).StringCellValue);
                }

                // 读取数据行
                for (int i = 1; i <= sheet.LastRowNum; i++)
                {
                    DataRow dataRow = dataTable.NewRow();
                    IRow row = sheet.GetRow(i);
                    for (int j = 0; j < row.LastCellNum; j++)
                    {
                        dataRow[j] = row.GetCell(j).ToString();
                    }
                    dataTable.Rows.Add(dataRow);
                }
            }

            return dataTable;
        }

        public void ExportExcelFile(DataTable dataTable, string filePath)
        {
            // 创建一个新的工作簿
            HSSFWorkbook workbook = new HSSFWorkbook();

            // 创建一个新的工作表
            ISheet sheet = workbook.CreateSheet("Sheet1");

            // 创建表头行
            IRow headerRow = sheet.CreateRow(0);
            sheet.SetColumnWidth(0, 20 * 256);
            sheet.SetColumnWidth(1, 20 * 256);
            for (int i = 0; i < dataTable.Columns.Count; i++)
            {
                headerRow.CreateCell(i).SetCellValue(dataTable.Columns[i].ColumnName);
            }

            // 添加数据行
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                IRow row = sheet.CreateRow(i + 1);
                for (int j = 0; j < dataTable.Columns.Count; j++)
                {
                    row.CreateCell(j).SetCellValue(dataTable.Rows[i][j].ToString());
                }
            }

            // 保存 Excel 文件
            using (FileStream fileStream = new FileStream(filePath, FileMode.Create))
            {
                workbook.Write(fileStream);
            }
        }
    }
}
    

